package info.batcloud.fanli.core.repository;

import info.batcloud.fanli.core.entity.UserCommissionOrder;
import info.batcloud.fanli.core.enums.CommissionAllotType;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.Date;
import java.util.List;

public interface UserCommissionOrderRepository extends PagingAndSortingRepository<UserCommissionOrder, Long>,
        JpaSpecificationExecutor<UserCommissionOrder> {

    long countByUserIdAndCreateTimeBetween(long userId, Date startTime, Date endTime);

    UserCommissionOrder findByUserIdAndCommissionOrderIdAndAllotType(long userId, long commissionOrderId, CommissionAllotType allotType);

    /**
     * 读取用户在某个日期之间的预估佣金金额
     */
    @Query(value = "select sum(uco.commission_rate * co.estimate_commission_fee / 100) from user_commission_order uco left join commission_order co " +
            "on uco.commission_order_id=co.id where uco.user_id=?1 and co.create_time between ?2 and ?3 and co.status != 'INVALID'", nativeQuery = true)
    Float findUserEstimateCommissionFeeByCreateTimeBetween(long userId, Date startTime, Date endTime);

    /**
     * 读取用户在某个日期之间的预估佣金金额
     */
    @Query(value = "select sum(uco.settled_fee) from user_commission_order uco where uco.user_id=?1 and uco.settled_time between ?2 and ?3", nativeQuery = true)
    Float findUserSettledCommissionFeeBySettledTimeBetween(long userId, Date startTime, Date endTime);

    /**
     * 读取用户在某个日期之间的预估佣金金额
     */
    @Query(value = "select convert(sum(uco.commission_rate * co.estimate_commission_fee / 100), decimal(10,2)) from user_commission_order uco left join commission_order co " +
            "on uco.commission_order_id=co.id where uco.user_id=?1 and co.create_time >= ?2 and co.status != 'INVALID'", nativeQuery = true)
    Float findUserEstimateCommissionFeeByCreateTimeIsAfter(long userId, Date startTime);

    /**
     * 读取用户所有的待结算的金额
     */
    @Query(value = "select sum(uco.commission_rate * co.estimate_commission_fee / 100) from user_commission_order uco left join commission_order co " +
            "on uco.commission_order_id=co.id where uco.user_id=?1 and co.status = 'WAIT_SETTLE'", nativeQuery = true)
    Float findUserWaitSettleEstimateCommissionFee(long userId);

    /**
     * 读取用户所有的已付款的金额
     */
    @Query(value = "select sum(uco.commission_rate * co.estimate_commission_fee / 100) from user_commission_order uco left join commission_order co " +
            "on uco.commission_order_id=co.id where uco.user_id=?1 and co.status = 'PAID'", nativeQuery = true)
    Float findUserPaidEstimateCommissionFee(long userId);

    /**
     * 读取用户所有的未结算的预估佣金,未结算订单包含 已付款、待结算
     */
    @Query(value = "select sum(uco.commission_rate * co.estimate_commission_fee / 100) from user_commission_order uco left join commission_order co " +
            "on uco.commission_order_id=co.id where uco.user_id=?1 and co.status in ('WAIT_SETTLE', 'PAID')", nativeQuery = true)
    Float findUserUnSettleEstimateCommissionFee(long userId);

    List<UserCommissionOrder> findByCommissionOrderId(long commissionOrderId);

}
